//package dao;
//
//import model.TbPerson;
//import model.TbTimecard;
//
//import org.springframework.dao.DataAccessException;
//import org.springframework.jdbc.core.JdbcTemplate;
//
//import tools.JDBCUtils;
//
//public class TimecardDao {
//	private static JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
//
//	public static void addTimecard(TbTimecard tc) {
//		try {
//            String sql = "insert into tb_timecard(timecard_id,person_id,timecard_date,ratifier_id,explains) values(?,?,?,?,?)";
//            template.update(sql,tc.getTimecardId(),tc.getPersonId(),tc.getTimecardDate(),tc.getRatifierId(),tc.getExplains());
//        } catch (DataAccessException e) {
//            e.printStackTrace();
//        }
//
//	}
//
//	public static int findTimecardCountByPersonId(int tcid,String recordNumber) {
//		try {
//			String sql = "select count(*) from tb_timecard where timecard_id = ? and person_id = ?";
//			int a = template.queryForObject(sql, Integer.class,tcid,recordNumber);
//			return a;
//		} catch (DataAccessException e) {
//			e.printStackTrace();
//			return 0;
//		}
//	}
//}

package dao;

import model.TbTimecard;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import tools.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class TimecardDao {
	private static JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

	// 添加打卡记录（适配你的实体类）
	public static void addTimecard(TbTimecard tc) {
		try {
			// 获取当前最大ID并+1（假设数据库支持自增或手动维护）
			int newId = getNextTimecardId();

			String sql = "insert into tb_timecard(timecard_id, person_id, timecard_date, ratifier_id, explains) values(?, ?, ?, ?, ?)";
			template.update(sql,
					newId,
					tc.getPersonId(),
					tc.getTimecardDate(),
					tc.getRatifierId(),
					tc.getExplains()
			);
		} catch (DataAccessException e) {
			e.printStackTrace();
			throw new RuntimeException("添加打卡记录失败", e);
		}
	}

	// 查询指定日期范围内的打卡记录
	public static List<TbTimecard> findTimecardsByPersonAndTime(String personId, Date startTime, Date endTime) {
		try {
			String sql = "SELECT * FROM tb_timecard WHERE person_id = ? AND timecard_date BETWEEN ? AND ? ORDER BY timecard_date ASC";
			return template.query(sql, new TimecardRowMapper(), personId, startTime, endTime);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 查询指定日期范围内的打卡记录（假设typeId映射到explains字段）
	public static List<TbTimecard> findTimecardsByPersonAndTypeAndTime(String personId, String type, Date startTime, Date endTime) {
		try {
			String sql = "SELECT * FROM tb_timecard WHERE person_id = ? AND explains LIKE ? AND timecard_date BETWEEN ? AND ? ORDER BY timecard_date ASC";
			return template.query(sql, new TimecardRowMapper(), personId, "%" + type + "%", startTime, endTime);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 查询某人当天的所有打卡记录
	public static List<TbTimecard> findTodayTimecardsByPerson(String personId, Date currentDate) {
		try {
			// 计算当天0点和23:59:59
			java.sql.Date startDate = new java.sql.Date(currentDate.getTime());
			java.sql.Date endDate = new java.sql.Date(currentDate.getTime() + 24 * 60 * 60 * 1000 - 1);

			String sql = "SELECT * FROM tb_timecard WHERE person_id = ? AND timecard_date BETWEEN ? AND ? ORDER BY timecard_date ASC";
			return template.query(sql, new TimecardRowMapper(), personId, startDate, endDate);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 获取某人最早的上班打卡时间（假设"上班"在explains字段中）
	public static Date findEarliestWorkTime(String personId, Date currentDate) {
		try {
			java.sql.Date startDate = new java.sql.Date(currentDate.getTime());
			java.sql.Date endDate = new java.sql.Date(currentDate.getTime() + 24 * 60 * 60 * 1000 - 1);

			String sql = "SELECT MIN(timecard_date) FROM tb_timecard WHERE person_id = ? AND explains LIKE ? AND timecard_date BETWEEN ? AND ?";
			return template.queryForObject(sql, Date.class, personId, "%上班%", startDate, endDate);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 获取某人最晚的下班打卡时间（假设"下班"在explains字段中）
	public static Date findLatestLeaveTime(String personId, Date currentDate) {
		try {
			java.sql.Date startDate = new java.sql.Date(currentDate.getTime());
			java.sql.Date endDate = new java.sql.Date(currentDate.getTime() + 24 * 60 * 60 * 1000 - 1);

			String sql = "SELECT MAX(timecard_date) FROM tb_timecard WHERE person_id = ? AND explains LIKE ? AND timecard_date BETWEEN ? AND ?";
			return template.queryForObject(sql, Date.class, personId, "%下班%", startDate, endDate);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	// 计算某人在某个时间段内的特定类型打卡次数（假设type映射到explains字段）
	public static int findTimecardCountByPersonId(String type, String personId, Date startTime, Date endTime) {
		try {
			String sql = "SELECT COUNT(*) FROM tb_timecard WHERE person_id = ? AND explains LIKE ? AND timecard_date BETWEEN ? AND ?";
			return template.queryForObject(sql, Integer.class, personId, "%" + type + "%", startTime, endTime);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}

	// 原有的统计打卡次数方法（保持不变）
	public static int findTimecardCountByPersonId(int tcid, String recordNumber) {
		try {
			String sql = "select count(*) from tb_timecard where timecard_id = ? and person_id = ?";
			return template.queryForObject(sql, Integer.class, tcid, recordNumber);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}

	// 获取下一个timecardId（简单实现，实际项目建议使用数据库自增）
	private static int getNextTimecardId() {
		try {
			String sql = "SELECT IFNULL(MAX(timecard_id), 0) + 1 FROM tb_timecard";
			return template.queryForObject(sql, Integer.class);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 1; // 默认从1开始
		}
	}



	// 内部类：将结果集映射为TbTimecard对象
	private static class TimecardRowMapper implements RowMapper<TbTimecard> {
		@Override
		public TbTimecard mapRow(ResultSet rs, int rowNum) throws SQLException {
			TbTimecard tc = new TbTimecard(
					rs.getInt("timecard_id"),
					rs.getString("person_id"),
					rs.getTimestamp("timecard_date"),
					rs.getString("ratifier_id"),
					rs.getString("explains")
			);
			return tc;
		}
	}
	/**
	 * 查询指定员工当天的最近一次打卡记录
	 */
	public static TbTimecard findLatestTimecardByPersonId(String personId, Date startOfDay) {
		String sql = "SELECT * FROM tb_timecard " +
				"WHERE person_id = ? AND timecard_date >= ? " +
				"ORDER BY timecard_date DESC LIMIT 1";
		return template.queryForObject(sql, new Object[]{personId, startOfDay}, new BeanPropertyRowMapper<>(TbTimecard.class));
	}

	/**
	 * 统计指定员工的某种打卡类型次数
	 */
	public static int countTimecardByType(String personId, String type) {
		String sql = "SELECT COUNT(*) FROM tb_timecard WHERE person_id = ? AND explains = ?";
		return template.queryForObject(sql, new Object[]{personId, type}, Integer.class);
	}
}